﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[FileReferenceWordHit]') AND type in (N'U'))
BEGIN
	DROP TABLE [esf_files].[FileReferenceWordHit]
END

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[FileReferenceLevel]') AND type in (N'U'))
BEGIN
	DROP TABLE [esf_files].FileReferenceLevel
END

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[FileReference]') AND type in (N'U'))
BEGIN
	DROP TABLE [esf_files].[FileReference]
END


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[esf_files].[FK_FilRef_BasRef_FileTypeID]') AND parent_object_id = OBJECT_ID(N'[esf_files].[FileReference]'))
ALTER TABLE [esf_files].[FileReference] DROP CONSTRAINT [FK_FilRef_BasRef_FileTypeID]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__FileRefer__IsEna__65E11278]') AND type = 'D')
BEGIN
ALTER TABLE [esf_files].[FileReference] DROP CONSTRAINT [DF__FileRefer__IsEna__65E11278]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__FileRefer__Downl__66D536B1]') AND type = 'D')
BEGIN
ALTER TABLE [esf_files].[FileReference] DROP CONSTRAINT [DF__FileRefer__Downl__66D536B1]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__FileRefer__OpenC__67C95AEA]') AND type = 'D')
BEGIN
ALTER TABLE [esf_files].[FileReference] DROP CONSTRAINT [DF__FileRefer__OpenC__67C95AEA]
END

GO


CREATE TABLE [esf_files].[FileReference](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](260) NULL,
	[URL] [varchar](2000) NOT NULL,
	[LengthBytes] [bigint] NULL,
	[FileTypeID] [int] NULL,
	[CreationDateTime] [datetime] NULL,
	[LastUpdateDateTime] [datetime] NULL,
	[LastWriteDateTime] [datetime] NULL,
	[VersionDateTime] [datetime] NULL,
	[MonitoredDirectoryID] [int] NULL,
	[MonitoredDirectorySubCategoryID] [int] NULL,
	[EnterpriseID] int NULL,
	[ImageFormat] [varchar](10) NULL,
	[OnlyName] [varchar](100) NULL,
	[OnlyExtension] [varchar](100) NULL,
	[ItemsCount] [int] NULL,
	[IsEnabled] [bit] NULL,
	[IsEnabledUserAlias] [varchar](20) NULL,
	[IsEnabledDateTime] [datetime] NULL,
	[DownloadCount] [int] NULL,
	[LastDownloadUserAlias] [varchar](20) NULL,
	[LastDownloadDateTime] [datetime] NULL,
	[OpenCount] [int] NULL,
	[LastOpenUserAlias] [varchar](20) NULL,
	[LastOpenDateTime] [datetime] NULL,
	[LastMoveToSubCategoryUserAlias] [varchar](20) NULL,
	[LastMoveToSubCategoryDateTime] [datetime] NULL,
	[LastUpdateAction] [varchar](50) NULL,
	ParentFileRelativeOrder INT NULL,
	ParentFileReferenceID INT NULL,
	ReadProcessingTimeMs INT NULL,
	URLMD5 VARCHAR (100)
 CONSTRAINT [PK_esf_files_Files] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [esf_files].[FileReference]  WITH CHECK ADD  CONSTRAINT [FK_FilRef_FilRef_ParentFileReferenceID] FOREIGN KEY([ParentFileReferenceID])
REFERENCES [esf_files].[FileReference] ([ID])
GO

ALTER TABLE [esf_files].[FileReference]  WITH CHECK ADD  CONSTRAINT [FK_FilRef_BasRef_FileTypeID] FOREIGN KEY([FileTypeID])
REFERENCES [esf_sso].[BasicReference] ([ID])
GO

ALTER TABLE [esf_files].[FileReference]  WITH CHECK ADD  CONSTRAINT [FK_FilRef_BasRef_EnterpriseID] FOREIGN KEY([EnterpriseID])
REFERENCES [esf_sso].[BasicReference] ([ID])
GO

ALTER TABLE [esf_files].[FileReference] CHECK CONSTRAINT [FK_FilRef_BasRef_FileTypeID]
GO

ALTER TABLE [esf_files].[FileReference] ADD  DEFAULT ((1)) FOR [IsEnabled]
GO

ALTER TABLE [esf_files].[FileReference] ADD  DEFAULT ((0)) FOR [DownloadCount]
GO

ALTER TABLE [esf_files].[FileReference] ADD  DEFAULT ((0)) FOR [OpenCount]
GO


-- ALTER TABLE esf_files.FileReference
-- ADD CONSTRAINT UQ_esf_files_Files_URLMonDir UNIQUE(URL, MonitoredDirectoryID)


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[esf_files].[FileReference]') AND name = N'IX_esf_files_Files_ParentFileReferenceID')
DROP INDEX [IX_esf_files_Files_ParentFileReferenceID] ON [esf_files].[FileReference] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_esf_files_Files_ParentFileReferenceID] ON [esf_files].[FileReference] 
(
	[ParentFileReferenceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_esf_files_Files_MonDir] ON [esf_files].[FileReference] 
(
	[MonitoredDirectoryID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[FileReferenceLevel]') AND type in (N'U'))
BEGIN
	DROP TABLE [esf_files].FileReferenceLevel
END


CREATE TABLE esf_files.FileReferenceLevel
(
	FileReferenceID INT, 
	Level INT,
	Name VARCHAR (1000) NULL,
	CONSTRAINT PK_FileReferenceLevel PRIMARY KEY (FileReferenceID, Level)
)


ALTER TABLE [esf_files].[FileReferenceLevel]  WITH CHECK ADD  CONSTRAINT [FK_FilRefLev_FilRef_FileReferenceID] FOREIGN KEY([FileReferenceID])
REFERENCES [esf_files].[FileReference] ([ID])
GO
ALTER TABLE [esf_files].[FileReference] CHECK CONSTRAINT [FK_FilRef_BasRef_FileTypeID]


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[esf_files].[FileReferenceLevel]') AND name = N'IX_FilRefLev_Level')
DROP INDEX [IX_FilRefLev_Level] ON [esf_files].[FileReferenceLevel] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_FilRefLev_Level] ON [esf_files].[FileReferenceLevel] 
(
	[Level] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]



/*
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[FileReferenceModel]') AND type in (N'U'))
BEGIN
	DROP TABLE [esf_files].[FileReferenceModel]
END


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[esf_files].[FK_FilRefMod_BasRef_MonitoredDirectoryID]') AND parent_object_id = OBJECT_ID(N'[esf_files].[FileReferenceModel]'))
ALTER TABLE [esf_files].[FileReferenceModel] DROP CONSTRAINT [FK_FilRefMod_BasRef_MonitoredDirectoryID]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[esf_files].[FK_FilRefMod_BasRef_UseBasicReferenceTypeID]') AND parent_object_id = OBJECT_ID(N'[esf_files].[FileReferenceModel]'))
ALTER TABLE [esf_files].[FileReferenceModel] DROP CONSTRAINT [FK_FilRefMod_BasRef_UseBasicReferenceTypeID]
GO


CREATE TABLE esf_files.FileReferenceModel
(
	MonitoredDirectoryID INT,
	[Level] INT,
	UseBasicReferenceTypeID INT NULL,
	UseDateTimeFormat VARCHAR (30) NULL,
	UseCustomStringRegEx VARCHAR (100) NULL,
	UseBarCodeForFileName BIT DEFAULT 0,
	UseConsecutive BIT DEFAULT 0
)

ALTER TABLE [esf_files].[FileReferenceModel]  WITH CHECK ADD  CONSTRAINT [FK_FilRefMod_BasRef_MonitoredDirectoryID] FOREIGN KEY([MonitoredDirectoryID])
REFERENCES [esf_sso].[BasicReference] ([ID])

ALTER TABLE [esf_files].[FileReferenceModel]  WITH CHECK ADD  CONSTRAINT [FK_FilRefMod_BasRef_UseBasicReferenceTypeID] FOREIGN KEY([UseBasicReferenceTypeID])
REFERENCES [esf_sso].[BasicReference] ([ID])


CREATE UNIQUE NONCLUSTERED INDEX [IX_FilRefMod] ON [esf_files].[FileReferenceModel] 
(
	[MonitoredDirectoryID] ASC,
	[Level] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[FileReferenceWordHit]') AND type in (N'U'))
BEGIN
	DROP TABLE [esf_files].[FileReferenceWordHit]
END


CREATE TABLE [esf_files].[FileReferenceWordHit]
(
	FileReferenceID INT,
	WordID INT,
	WordText VARCHAR (500),
	PageIndex INT NULL,
	WordPreviousID INT NULL,
	X0 INT,
	Y0 INT, 
	X1 INT, 
	Y1 INT
)

ALTER TABLE [esf_files].[FileReferenceWordHit]  WITH CHECK ADD  CONSTRAINT [FK_FileReferenceWordHit_BasRef_WordID] FOREIGN KEY([WordID])
REFERENCES [esf_sso].[BasicReference] ([ID])

ALTER TABLE [esf_files].[FileReferenceWordHit]  WITH CHECK ADD  CONSTRAINT [FK_FileReferenceWordHit_BasRef_WordPreviousID] FOREIGN KEY([WordPreviousID])
REFERENCES [esf_sso].[BasicReference] ([ID])

ALTER TABLE [esf_files].[FileReferenceWordHit]  WITH CHECK ADD  CONSTRAINT [FK_FileReferenceWordHit_FileRef_ID] FOREIGN KEY([FileReferenceID])
REFERENCES [esf_files].[FileReference] ([ID])



